#include <iostream>
#include <pqxx/pqxx> 
#include <fstream>
#include <sstream>
using namespace std;
using namespace pqxx;

int main(int argc, char* argv[])
{
   int year = 2011;
   //ofstream ndvi, lswi, evi;
   //ndvi.open("ndvi8.txt");
   //lswi.open("lswi8.txt");
   //evi.open("evi8.txt");

   //int left_coo = 2232; int right_coo = 2394;
   //int top_coo = 2225;  int bot_coo = 2334;

   int line[] = {2333, 2340, 2350, 2360, 2361, 2339, 2369, 2370};
   int sample[] = {2251, 2260, 2271, 2283, 2286, 2299, 2306, 2306};
   
   string sql;
   stringstream sqlstream;
   
   try{
      connection C("dbname=mymodisdb user=demo1usr password=demo1pwd hostaddr=127.0.0.1 port=5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can't open database" << endl;
         return 1;
      }

      //int total_cloud; int max_cloud = 0;
      //for ( int x = left_coo ; x <= right_coo ; x++ )
      double ndvi_avg8pt[46];
      double evi_avg8pt[46];
      double lswi_avg8pt[46];
      for (int i = 0 ; i < 46 ; i ++ ){
         ndvi_avg8pt[i] = 0;
         lswi_avg8pt[i] = 0;
         evi_avg8pt[i] = 0;
      }
      for ( int cnt = 0; cnt < 8; cnt++ )
      {
         //for (int y = top_coo ; y <= bot_coo ; y++ )
         //{
            int x = line[cnt];
            int y = sample[cnt];
            //ndvi << x << " " << y << "\t";
            //lswi << x << " " << y << "\t";
            //evi << x << " " << y << "\t";
            sqlstream.str("");//clear stream
            sqlstream << "\
            SELECT pixels.px_x, pixels.px_y, pixels.date_tkn, pixels.ndvi, pixels.lswi, pixels.evi, pixels.sur_refl_b03 \
            FROM public.pixels \
            WHERE px_x = "<< x <<" and px_y = "<< y <<" \
            ORDER BY pixels.date_tkn \
            LIMIT 46 OFFSET "<< 46*(year-2009);
            sql = sqlstream.str();

            // query
            nontransaction N(C);      
            result R( N.exec( sql ));
            //total_cloud = 0;
            double ndvi_curr, evi_curr, lswi_curr;
            int curr_circle = 0;
            for (result::const_iterator c = R.begin(); c != R.end(); ++c)
            {
               // threshold to masked a pixel to be cloud and exclude in further analyze, according to Xiao's paper
               if ( c[6].as<double>() < 2000 ){
                  //ndvi << c[3].as<double>() << "\t";
                  ndvi_curr = c[3].as<double>(); 
                  //lswi << c[4].as<double>() << "\t";
                  lswi_curr = c[4].as<double>();
                  //evi << c[5].as<double>() << "\t";
                  evi_curr = c[5].as<double>();
               } else {
                  //ndvi << 0 << "\t";
                  ndvi_curr = 0;
                  //lswi << 0 << "\t";
                  lswi_curr = 0;
                  //evi << 0 << "\t";
                  evi_curr = 0;
                  //total_cloud++;
               }
               ndvi_avg8pt[curr_circle] += ndvi_curr;
               lswi_avg8pt[curr_circle] += lswi_curr;
               evi_avg8pt[curr_circle] += evi_curr;
               curr_circle++;
            }
            //if ( total_cloud > max_cloud ) max_cloud = total_cloud;
            //ndvi << endl;
            //lswi << endl;
            //evi << endl;
         //}
         //cout << "\t" << cnt << "/8"<< endl;
      }

      ofstream ndvi, lswi, evi;
      ndvi.open("ndvi_avg8pt.txt");
      lswi.open("lswi_avg8pt.txt");
      evi.open("evi_avg8pt.txt");
      for (int i = 0; i < 46; i++){
         ndvi << ndvi_avg8pt[i]/8.0 << "\t";
         lswi << lswi_avg8pt[i]/8.0 << "\t";
         evi << evi_avg8pt[i]/8.0 << "\t";
      }
      ndvi.close();
      lswi.close();
      evi.close();

      cout << "Operation done successfully!" << endl;
      //cout << "Max cloud a year for a pixel:\t" << max_cloud << endl;
      C.disconnect ();
   }catch (const std::exception &e){
      cerr << e.what() << std::endl;
      return 1;
   }
   return 0;
}